﻿using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Globalization;
using ContractManagementSystem.Entity;

namespace ContractManagementSystem.DataAccess
{
    public class ContractDataAccess : LogDataAccess
    {
        //readonly LogDataAccess _logDataAccess = new LogDataAccess();
        private const string UserName = "Admin";

        //public ContractDataAccess(string userName)
        //{
        //    UserName = 
        //}

        /// <summary>
        /// Function Insert Contract
        /// </summary>
        /// <param name="contractEntity"></param>
        /// <returns>
        /// 1 if success.0 if fail
        /// </returns>
        public int Insert(ContractEntity contractEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return 0;
            }

            int result = 0;
            try
            {
                //log event          
                //_logDataAccess.WriteEvent("Inserting...", UserName);
                WriteEvent("Inserting...", UserName);
                using (var sqlCommand = new SqlCommand("sp_Contract_Insert", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@contractorId", contractEntity.ContractorId);
                    sqlCommand.Parameters.AddWithValue("@contractTypeId", contractEntity.ContractTypeId);
                    sqlCommand.Parameters.AddWithValue("@managerId", contractEntity.ManagerId);
                    sqlCommand.Parameters.AddWithValue("@consultantId", contractEntity.ConsultantId);
                    sqlCommand.Parameters.AddWithValue("@content", contractEntity.Content);
                    sqlCommand.Parameters.AddWithValue("@startDate", contractEntity.StartDate);
                    sqlCommand.Parameters.AddWithValue("@endDate", contractEntity.EndDate);

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;

                    //log event
                    WriteEvent("Insert success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            catch (NullReferenceException nullReferenceException)
            {
                ErrorMessage = nullReferenceException.Message;

                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Update Contract
        /// </summary>
        /// <param name="contractEntity"></param>
        /// <returns>
        /// 1 if success.0 if fail
        /// </returns>
        public int Update(ContractEntity contractEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return 0;
            }

            int result = 0;
            try
            {
                //log event                
                WriteEvent("Updating...", UserName);
                using (var sqlCommand = new SqlCommand("sp_Contract_Update", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", contractEntity.ContractId);
                    sqlCommand.Parameters.AddWithValue("@contractorId", contractEntity.ContractorId);
                    sqlCommand.Parameters.AddWithValue("@contractTypeId", contractEntity.ContractTypeId);
                    sqlCommand.Parameters.AddWithValue("@managerId", contractEntity.ManagerId);
                    sqlCommand.Parameters.AddWithValue("@consultantId", contractEntity.ConsultantId);
                    sqlCommand.Parameters.AddWithValue("@content", contractEntity.Content);

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Update success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            catch (SqlTypeException sqlTypeException)
            {
                ErrorMessage = sqlTypeException.Message;

                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            catch (NullReferenceException nullReferenceException)
            {
                ErrorMessage = nullReferenceException.Message;

                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Delete Contract
        /// </summary>
        /// <param name="id"></param>
        /// <returns>
        /// 1 if success.0 if fail
        /// </returns>
        public int Delete(string id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return 0;
            }

            int result = 0;
            try
            {
                //log event                
                WriteEvent("Deleting...", UserName);
                using (var sqlCommand = new SqlCommand("sp_Contract_Delete", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@id", id));

                    result = sqlCommand.ExecuteNonQuery();
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Delete success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Delete fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function Select Contract
        /// </summary>
        /// <returns></returns>
        public new DataTable FetchAll()
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            DataTable result = null;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);
                using (var sqlCommand = new SqlCommand("sp_Contract_Select", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    var adapter = new SqlDataAdapter(sqlCommand);
                    result = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(result);
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public DataTable FetchAllWithContractor(string fullName,string contractTypeId,string startDate,string endDate)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            DataTable result = null;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);
                if (String.IsNullOrEmpty(contractTypeId))
                {
                    contractTypeId = "00000000-0000-000000000-000000000000";
                }

                using (var sqlCommand = new SqlCommand("sp_Contract_SearchByFullName", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@fullname", fullName);
                    sqlCommand.Parameters.AddWithValue("@contractTypeId", contractTypeId);
                    sqlCommand.Parameters.AddWithValue("@startDate", startDate);
                    sqlCommand.Parameters.AddWithValue("@endDate", endDate);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    result = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(result);
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        public DataTable FetchAllWithContractType(Guid id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }

            DataTable result = null;
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);
                using (var sqlCommand = new SqlCommand("sp_Contract_SearchByContractType", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", id);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    result = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(result);
                    ErrorMessage = null;
                    //log event
                    WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;

                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);

                return result;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Function SelectById Contract
        /// Contractor: "sp_Contract_SelectByContractorId"
        /// Consultant: "sp_Contract_SelectByConsultantId"
        /// Manager: "sp_Contract_SelectByManagerId"
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable FetchById(string spName, string id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return null;
            }

            DataTable result = null;
            try
            {
                using (var sqlCommand = new SqlCommand(spName, CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", id);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    result = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(result);
                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }

        /// <summary>
        /// Get EndDate, StartDate of Contract
        /// </summary>
        /// <param name="contractId"></param>
        /// <returns></returns>
        public DataTable FetchContractDateById(Guid contractId)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("StartDate", typeof(DateTime));
            dataTable.Columns.Add("EndDate", typeof(DateTime));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Contract_GetDate", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@ContractId", contractId));
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            DateTime startDate = Convert.ToDateTime(sqlDataReader["StartDate"], CultureInfo.CurrentCulture);
                            DateTime endDate = Convert.ToDateTime(sqlDataReader["EndDate"], CultureInfo.CurrentCulture);

                            dataTable.Rows.Add(startDate, endDate);
                        }
                    }

                    sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public DataTable FetchContractIdByUserId(Guid userId)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("ContractId", typeof(Guid));
            dataTable.Columns.Add("Name", typeof(string));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Contract_SelectContractIdByUserId", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@UserId", userId));
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            var contractId = new Guid(sqlDataReader["ContractId"].ToString());
                            string name = sqlDataReader["Name"].ToString();

                            dataTable.Rows.Add(contractId, name);
                        }
                    }

                    sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="contractorId"></param>
        /// <param name="keyId"></param>
        /// <param name="role"></param>
        /// <returns></returns>
        public DataTable FetchContractByKey(Guid contractorId, Guid keyId, char role)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("ContractId", typeof(Guid));
            dataTable.Columns.Add("Name", typeof(string));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Contract_GetByContratorIdAndKey", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@Key", role));
                    sqlCommand.Parameters.Add(new SqlParameter("@ContractorId", contractorId));
                    sqlCommand.Parameters.Add(new SqlParameter("@KeyId", keyId));
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            var contractId = new Guid(sqlDataReader["ContractId"].ToString());
                            string name = sqlDataReader["Name"].ToString();

                            dataTable.Rows.Add(contractId, name);
                        }
                    }

                    sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="keyId"></param>
        /// <param name="role"></param>
        /// <returns></returns>
        public DataTable FetchContractorByKey(Guid keyId, char role)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("ContractorId", typeof(Guid));
            dataTable.Columns.Add("FullName", typeof(string));

            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return dataTable;
            }
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Contract_GetContractorIdByKey", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@Key", role));
                    sqlCommand.Parameters.Add(new SqlParameter("@KeyId", keyId));
                    SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                    if (sqlDataReader.HasRows)
                    {
                        while (sqlDataReader.Read())
                        {
                            var contractorId = new Guid(sqlDataReader["ContractorId"].ToString());
                            string fullName = sqlDataReader["FullName"].ToString();

                            dataTable.Rows.Add(contractorId, fullName);
                        }
                    }

                    sqlDataReader.Close();
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        public ContractEntity FetchById(string id)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                return null;
            }

            ContractEntity result = null;
            try
            {
                using (var sqlCommand = new SqlCommand("sp_Contract_SelectByContractId", CreateConnection()))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.AddWithValue("@id", id);

                    var adapter = new SqlDataAdapter(sqlCommand);
                    var table = new DataTable { Locale = CultureInfo.CurrentCulture };
                    adapter.Fill(table);

                    result = new ContractEntity
                                 {
                                     ContractId = new Guid(table.Rows[0][0].ToString()),
                                     ContractorId = new Guid(table.Rows[0][1].ToString()),
                                     ContractTypeId = new Guid(table.Rows[0][2].ToString()),
                                     ManagerId = new Guid(table.Rows[0][3].ToString()),
                                     ConsultantId = new Guid(table.Rows[0][4].ToString()),
                                     Content = table.Rows[0][5].ToString(),
                                     StartDate = DateTime.Parse(table.Rows[0][6].ToString(), CultureInfo.CurrentCulture),
                                     EndDate = DateTime.Parse(table.Rows[0][7].ToString(), CultureInfo.CurrentCulture)
                                 };

                    ErrorMessage = null;
                }
            }
            catch (SqlException sqlException)
            {
                ErrorMessage = sqlException.Message;
            }
            finally
            {
                CloseConnection();
            }

            return result;
        }
    }
}
